﻿package zuoye;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.DBUtil;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

/*
 * 
 3：写好StudentDao，实现必要的CRUD功能（包括分页）
 */
public class StudentDao implements IstudentDao {
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.IStudent#findALL()
	 */
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.IstudentDao#findAll()
	 */
	@Override
	// 查看所有学生的全部信息
	public List<Student> findAll() {
		String sql = "select * from s_student";
		List<Student> list = new ArrayList<Student>();

		Connection con = (Connection) util.DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;

		try {
			st = (PreparedStatement) con.prepareStatement(sql);
			rs = st.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getLong("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setTelephone(rs.getString("telephone"));
				s.setQq(rs.getString("qq"));
				s.setWechat(rs.getString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setEnterdate(rs.getString("enterdate"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getString("birthday"));
				s.setAddress(rs.getString("address"));
				s.setPass(rs.getString("pass"));
				list.add(s);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	// 增加学生信息
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.IstudentDao#add(zuoye.Student)
	 */
	@Override
	public int add(Student s) {
		String sql = "insert into s_student(sname,telephone,qq,wechat,fromcity,address,sex,gradeid,enterdate,birthday,pass)"
				+ "values(?,?,?,?,?,?,?,?,?,?,?)";

		Connection con = (Connection) util.DBUtil.getConnection();
		PreparedStatement st = null;

		try {
			st = (PreparedStatement) con.prepareStatement(sql);
			st.setString(1, s.getSname());
			st.setString(2, s.getTelephone());
			st.setString(3, s.getQq());
			st.setString(4, s.getWechat());
			st.setString(5, s.getFromcity());
			st.setString(6, s.getAddress());
			st.setString(7, s.getSex());
			st.setInt(8, s.getGradeid());
			st.setString(9, s.getEnterdate());
			st.setString(10, s.getBirthday());
			st.setString(11, s.getPass());

			return st.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(null, st, con);
		}
		return 0;
	}

	// 修改学生信息
	// 修改了返回1，未修改成功返回0.

	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.IstudentDao#update(zuoye.Student)
	 */
	@Override
	public int update(Student s) {
		Connection con = (Connection) DBUtil.getConnection();
		PreparedStatement st = null;

		try {
			// 用占位符
			String sql = "UPDATE s_student set sname=?,telephone=?,qq=?,wechat=?,fromcity=?,address=?,sex=?,gradeid=?,enterdate=?,birthday=?,pass=? where stuno=?";
			st = (PreparedStatement) con.prepareStatement(sql);

			st.setString(1, s.getSname());
			st.setString(2, s.getTelephone());
			st.setString(3, s.getQq());
			st.setString(4, s.getWechat());
			st.setString(5, s.getFromcity());
			st.setString(6, s.getAddress());
			st.setString(7, s.getSex());
			st.setInt(8, s.getGradeid());
			st.setString(9, s.getEnterdate());
			st.setString(10, s.getBirthday());
			st.setLong(11, s.getStuno());
			st.setString(12, s.getPass());

			return st.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(null, st, con);
		}
		return 0;
	}

	// 根据学号删除学生信息

	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.IstudentDao#del(zuoye.Student)
	 */
	@Override
	public int del(Student s) {
		Connection con = (Connection) DBUtil.getConnection();
		PreparedStatement st = null;
		try {
			String sql = "delete  from s_student where stuno= ?";
			st = (PreparedStatement) con.prepareStatement(sql);
			st.setLong(1, s.getStuno());

			return st.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return 0;
	}

	// 分页
	// pageNo页数 pageSize:页大小
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.IstudentDao#paging(int, int)
	 */
	@Override
	public List<Student> paging(int pageNo, int pageSize) {

		String sql = "select * from s_student  limit ?,?";
		List<Student> list = new ArrayList<>();
		Connection con = (Connection) DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = (PreparedStatement) con.prepareStatement(sql);
			st.setInt(1, (pageNo - 1) * pageSize);
			st.setInt(2, pageSize);
			rs = st.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setTelephone(rs.getString("telephone"));
				s.setQq(rs.getString("qq"));
				s.setWechat(rs.getString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setEnterdate(rs.getString("enterdate"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getString("birthday"));
				s.setAddress(rs.getString("address"));
				s.setPass(rs.getString("pass"));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return list;

	}

	// 统计
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.IstudentDao#total()
	 */
	@Override
	public int total() {
		Connection con = (Connection) DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select count(*)  from s_student";
		int total = 0;
		try {
			st = (PreparedStatement) con.prepareStatement(sql);
			rs = st.executeQuery();

			if (rs.next()) {
				total = rs.getInt(1);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return total;

	}

}
